﻿// Copyright © 2010 Silvestru Iapascurta.
// This source is subject to the Microsoft Public License (Ms-PL).
// Please see license.txt for details.
// All other rights reserved.

using System;
using FeedDotNet.Common;
using System.Data.SQLite;
using System.Data;
using System.IO;
using System.Windows.Forms;

namespace Karoro
{
    static class Database
    {
        static SQLiteConnection connection = new SQLiteConnection(String.Format("data source = \"{0}\"", System.IO.Path.Combine(Path.GetDirectoryName(Application.LocalUserAppDataPath), "karoro.db")));
        private static SQLiteDataAdapter sda;
        static string timerange = "";
        static string orderBy = "";
        static string select = "";

        public static long AddFeed(Feed feed, string folder_id)
        {
            SQLiteTransaction DbTransaction = null;
            if (String.IsNullOrEmpty(folder_id))
            {
                folder_id = "0";
            }

            try
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                DbTransaction = connection.BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(connection);
                command.CommandText =
                    "INSERT OR ABORT INTO feeds (feed_id, folder_id, xml_url, web_url, title) " +
                    "VALUES ((SELECT MAX(feed_id) FROM feeds) + 1, @folder_id, @xml_url, @web_url, @title)";
                command.Parameters.Add(new SQLiteParameter("@xml_url", feed.XmlUri.Uri));
                command.Parameters.Add(new SQLiteParameter("@web_url", feed.WebUri.Uri));
                command.Parameters.Add(new SQLiteParameter("@title", feed.Title));
                command.Parameters.Add(new SQLiteParameter("@folder_id", folder_id));
                command.ExecuteNonQuery();

                command.CommandText = "SELECT last_insert_rowid()";
                object result_feed_id = command.ExecuteScalar();

                using (SQLiteCommand itemCommand = connection.CreateCommand())
                {

                    itemCommand.CommandText =
                        "INSERT OR ABORT INTO items (item_id, feed_id, url, title, author, content, published_time) " +
                        "VALUES ((SELECT MAX(item_id) FROM items) + 1, @feed_id, @url, @title, @author, @content, @published_time)";
                    SQLiteParameter feed_id = new SQLiteParameter("@feed_id");
                    SQLiteParameter url = new SQLiteParameter("@url");
                    SQLiteParameter title = new SQLiteParameter("@title");
                    SQLiteParameter author = new SQLiteParameter("@author");
                    SQLiteParameter content = new SQLiteParameter("@content");
                    SQLiteParameter published_time = new SQLiteParameter("@published_time");

                    itemCommand.Parameters.AddRange(new SQLiteParameter[] { feed_id, url, title, author, content, published_time });

                    for (int i = feed.Items.Count - 1; i >= 0; i--)
                    {
                        // place holder variables
                        string authorValue = null;
                        DateTime PublishedTimeValue = DateTime.MinValue;
                        string ContentValue = null;
                        string TitleValue = null;
                        string UrlValue = null;
                        string UrlCommentsValue = null;

                        // get the dublincore module
                        FeedDotNet.Modules.DublinCore.ModuleItem dc_mod = null;
                        if (feed.Items[i].ModuleItems.ContainsKey("dc"))
                            dc_mod = (FeedDotNet.Modules.DublinCore.ModuleItem)feed.Items[i].GetModuleItem("dc");
                        // get the content module
                        FeedDotNet.Modules.Content.ModuleItem content_mod = null;
                        if (feed.Items[i].ModuleItems.ContainsKey("content"))
                            content_mod = (FeedDotNet.Modules.Content.ModuleItem)feed.Items[i].GetModuleItem("content");

                        // url
                        if (feed.Items[i].WebUris.Count != 0)
                            UrlValue = feed.Items[i].WebUris[0].Uri;
                        // comments url
                        if (!String.IsNullOrEmpty(feed.Items[i].CommentsUri))
                            UrlCommentsValue = feed.Items[i].CommentsUri;
                        // title
                        TitleValue = feed.Items[i].Title;
                        if (dc_mod != null && !String.IsNullOrEmpty(dc_mod.TitleDC))
                            TitleValue = dc_mod.TitleDC;

                        // authors (concatenate with ";")
                        if (feed.Items[i].Authors.Count != 0)
                        {
                            authorValue = feed.Items[i].Authors[0].Name;
                        }
                        if (dc_mod != null && !String.IsNullOrEmpty(dc_mod.Creator))
                            authorValue = dc_mod.Creator;

                        // content
                        int regular_length = feed.Items[i].ContentOrSummary.Length;
                        int content_length = 0;
                        int dc_length = 0;

                        if (content_mod != null)
                            content_length = content_mod.Encoded.Length;
                        if (dc_mod != null)
                            dc_length = dc_mod.Description.Length;

                        if (regular_length > content_length && regular_length > dc_length)
                            ContentValue = feed.Items[i].ContentOrSummary;

                        if (content_length > regular_length && content_length > dc_length)
                            ContentValue = content_mod.Encoded;

                        if (dc_length > regular_length && dc_length > content_length)
                            ContentValue = dc_mod.Description;

                        // published_time (minimal value of zero)
                        if (feed.Items[i].Published.HasValue)
                        {
                            PublishedTimeValue = feed.Items[i].Published.Value;
                        }
                        else if (dc_mod != null && dc_mod.Date.HasValue)
                        {
                            PublishedTimeValue = dc_mod.Date.Value;
                        }

                        feed_id.Value = result_feed_id;
                        url.Value = UrlValue;
                        title.Value = TitleValue;
                        author.Value = authorValue;
                        content.Value = ContentValue;
                        published_time.Value = PublishedTimeValue;

                        itemCommand.ExecuteNonQuery();
                    }
                }

                DbTransaction.Commit();
                return (long)result_feed_id;
            }
            finally
            {
                if (DbTransaction != null)
                    DbTransaction.Dispose();
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        public static int UpdateFeed(int feed_id, Feed feed)
        {
            SQLiteTransaction DbTransaction = null;
            int result = 0;

            try
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                DbTransaction = connection.BeginTransaction();

                using (SQLiteCommand itemCommand = connection.CreateCommand())
                {
                    itemCommand.CommandText =
                        "INSERT OR IGNORE INTO items (item_id, feed_id, url, title, author, content, published_time) VALUES ((SELECT MAX(item_id) FROM items) + 1, @feed_id, @url, @title, @author, @content, @published_time)";
                    SQLiteParameter feedid = new SQLiteParameter("@feed_id");
                    SQLiteParameter url = new SQLiteParameter("@url");
                    SQLiteParameter title = new SQLiteParameter("@title");
                    SQLiteParameter author = new SQLiteParameter("@author");
                    SQLiteParameter content = new SQLiteParameter("@content");
                    SQLiteParameter published_time = new SQLiteParameter("@published_time");

                    itemCommand.Parameters.AddRange(new SQLiteParameter[] { feedid, url, title, author, content, published_time });

                    for (int i = feed.Items.Count - 1; i >= 0; i--)
                    {
                        // place holder variables
                        string authorValue = null;
                        DateTime PublishedTimeValue = DateTime.MinValue;
                        string ContentValue = null;
                        string TitleValue = null;
                        string UrlValue = null;
                        string UrlCommentsValue = null;

                        // get the dublincore module
                        FeedDotNet.Modules.DublinCore.ModuleItem dc_mod = null;
                        if (feed.Items[i].ModuleItems.ContainsKey("dc"))
                            dc_mod = (FeedDotNet.Modules.DublinCore.ModuleItem)feed.Items[i].GetModuleItem("dc");
                        // get the content module
                        FeedDotNet.Modules.Content.ModuleItem content_mod = null;
                        if (feed.Items[i].ModuleItems.ContainsKey("content"))
                            content_mod = (FeedDotNet.Modules.Content.ModuleItem)feed.Items[i].GetModuleItem("content");

                        // url
                        if (feed.Items[i].WebUris.Count != 0)
                            UrlValue = feed.Items[i].WebUris[0].Uri;
                        // comments url
                        if (!String.IsNullOrEmpty(feed.Items[i].CommentsUri))
                            UrlCommentsValue = feed.Items[i].CommentsUri;
                        // title
                        TitleValue = feed.Items[i].Title;
                        if (dc_mod != null && !String.IsNullOrEmpty(dc_mod.TitleDC))
                            TitleValue = dc_mod.TitleDC;

                        if (feed.Items[i].Authors.Count != 0)
                        {
                            authorValue = feed.Items[i].Authors[0].Name;
                        }
                        if (dc_mod != null && !String.IsNullOrEmpty(dc_mod.Creator))
                            authorValue = dc_mod.Creator;

                        // content
                        int regular_length = feed.Items[i].ContentOrSummary.Length;
                        int content_length = 0;
                        int dc_length = 0;

                        if (content_mod != null)
                            content_length = content_mod.Encoded.Length;
                        if (dc_mod != null)
                            dc_length = dc_mod.Description.Length;

                        if (regular_length > content_length && regular_length > dc_length)
                            ContentValue = feed.Items[i].ContentOrSummary;

                        if (content_length > regular_length && content_length > dc_length)
                            ContentValue = content_mod.Encoded;

                        if (dc_length > regular_length && dc_length > content_length)
                            ContentValue = dc_mod.Description;

                        // published_time (minimal value of zero)
                        if (feed.Items[i].Published.HasValue)
                        {
                            PublishedTimeValue = feed.Items[i].Published.Value;
                        }
                        else if (dc_mod != null && dc_mod.Date.HasValue)
                        {
                            PublishedTimeValue = dc_mod.Date.Value;
                        }

                        feedid.Value = feed_id;
                        url.Value = UrlValue;
                        title.Value = TitleValue;
                        author.Value = authorValue;
                        content.Value = ContentValue;
                        published_time.Value = PublishedTimeValue;

                        result += itemCommand.ExecuteNonQuery();
                    }
                }

                DbTransaction.Commit();

                return result;
            }
            finally
            {
                if (DbTransaction != null)
                    DbTransaction.Dispose();
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }
        
        private static void Initialize()
        {
            switch (Karoro.Properties.Settings.Default.timespanView)
            {
                case 0:
                    timerange = "AND date(published_time) BETWEEN date('now', '-24 hours') AND date('now') ";
                    break;
                case 1:
                    timerange = "AND date(published_time) BETWEEN date('now', '-48 hours') AND date('now') ";
                    break;
                case 2:
                    timerange = "AND date(published_time) BETWEEN date('now', '-7 days') AND date('now') ";
                    break;
                case 3:
                    timerange = "AND date(published_time) BETWEEN date('now', '-1 month') AND date('now') ";
                    break;
                case 4:
                    timerange = "";
                    break;
                default:
                    break;
            }

            switch (Karoro.Properties.Settings.Default.sortByView)
            {
                case 0:
                    select = "SELECT *, feeds.title AS group_name, feeds.title AS feed_name ";
                    orderBy = String.Format("ORDER BY feed_id, published_time DESC ");
                    break;
                case 1:
                    select = "SELECT *, date(published_time) AS group_name, feeds.title AS feed_name ";
                    orderBy = String.Format("ORDER BY published_time DESC ");
                    break;
                default:
                    break;
            }
        }

        public static DataTable ReadFeed(int type, int id, int startIndex)
        {
            DataTable dt = new DataTable();
            string limit = String.Format("LIMIT {0}, {1} ", startIndex, Karoro.Properties.Settings.Default.itemsPerPage);
            
            try
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                Initialize();
                SQLiteCommand command = new SQLiteCommand(connection);
                
                switch (type)
                {
                    // feed
                    case 0:
                        command.CommandText = String.Format(
                                select +
                                "FROM items " +
                                "JOIN feeds ON (items.feed_id = feeds.feed_id) " +
                                "WHERE items.feed_id = {0} " + timerange +
                                orderBy + limit, id);  
                        break;

                    // folder
                    case 1:
                        command.CommandText = String.Format(
                            select +
                            "FROM items " +
                            "JOIN feeds ON (items.feed_id = feeds.feed_id) " +
                            "WHERE feeds.folder_id = {0} "  + timerange +
                            orderBy + limit, id);
                        break;

                    default:
                        break;
                }

                sda = new SQLiteDataAdapter(command);
                sda.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        public static string GetUpdateUrl(int feed_id)
        {
            string result;

            try
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                using (SQLiteCommand urlCommand = connection.CreateCommand())
                {
                    urlCommand.CommandText =
                        "SELECT xml_url FROM feeds WHERE feed_id = @feed_id";
                    SQLiteParameter feedid = new SQLiteParameter("@feed_id", feed_id);
                    urlCommand.Parameters.Add(feedid);

                    result = (string)urlCommand.ExecuteScalar();
                }

                return result;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        public static long ExecuteQuery(string command)
        {
            try
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                using (SQLiteCommand urlCommand = connection.CreateCommand())
                {
                    urlCommand.CommandText = command;
                    object result = urlCommand.ExecuteScalar();

                    if (result is DBNull)
                        return 0;
                    else
                        return (long)result;
                }
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        public static void Execute(string command, SQLiteParameter[] parameters)
        {
            try
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                using (SQLiteCommand urlCommand = connection.CreateCommand())
                {
                    urlCommand.CommandText = command;
                    if (parameters != null)
                        urlCommand.Parameters.AddRange(parameters);

                    urlCommand.ExecuteNonQuery();
                }

            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        public static long CreateFolder(string name)
        {
            long result;

            try
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                using (SQLiteCommand createCommand = connection.CreateCommand())
                {
                    createCommand.CommandText =
                        "INSERT INTO folders VALUES ((SELECT MAX(folder_id) FROM folders) + 1, @name); SELECT last_insert_rowid()";
                    SQLiteParameter folder_name = new SQLiteParameter("@name", name);
                    createCommand.Parameters.Add(folder_name);

                    result = (long)createCommand.ExecuteScalar();
                }

                return result;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        public static long GetItemCount(string type, string id)
        {
            Initialize();
            if (type == "feed")
            {
                return ExecuteQuery(String.Format(
                        "SELECT COUNT(item_id) " +
                        "FROM items " +
                        "WHERE feed_id = {0} " + timerange, id));
            }
            else if (type == "folder")
            {
                return ExecuteQuery(String.Format(
                        "SELECT COUNT(items.item_id) " +
                        "FROM items " +
                        "JOIN feeds ON (items.feed_id = feeds.feed_id) " +
                        "WHERE feeds.folder_id = {0} " + timerange, id));
            }
            else
                return 0;
        }

        public static DataTable Query(string command)
        {
            DataTable data = new DataTable();

            try
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                sda = new SQLiteDataAdapter(command, connection);
                sda.Fill(data);
            }
            catch (SQLiteException ex)
            {
                // this is a bit unreliable, but we DO populate the tree first, so this is the first one called
                if (ex.ErrorCode == SQLiteErrorCode.Error)
                    Execute(Karoro.Properties.Resources.db_tables, null);
                else
                    throw ex;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }

            return data;
        }
    }
}
